package com.github.common.extend.demo.util.excel.convert;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.DateTimeFormatProperty;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.apache.commons.lang3.StringUtils;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.MessageFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.time.temporal.ChronoUnit;
import java.util.Optional;


/**
 * @author wangjj7
 * @date 2023/2/22
 * @description
 */
public class CellUtils {

    /*debug发现多了两天*/
    private final static int PLUS_DAYS = -2;

    /**
     * 由于浮点数精度问题（从单元格读取的浮点数精度丢失），可能会出现相差一秒的情况
     * 测试了几个数据都是少一秒，是否会出现多一秒取决与精度保留策略
     * @param datetimeNumber
     * @return
     */
    public static LocalDateTime number2LocalDateTime(BigDecimal datetimeNumber)
    {
        BigDecimal fractionalPart = datetimeNumber.subtract(datetimeNumber.setScale(0, RoundingMode.DOWN));
        LocalDate localDate = startLocalDate().plusDays(datetimeNumber.intValue() + PLUS_DAYS);
        LocalTime localTime = fractionalPart.compareTo(BigDecimal.ZERO) > 0 ?
                LocalTime.ofSecondOfDay(fractionalPart.divideToIntegralValue(intervalBySecond()).intValue()) : LocalTime.MIN;
        LocalDateTime localDateTime = LocalDateTime.of(localDate, localTime);
        return localDateTime;
    }

    public static BigDecimal localDateTime2Number(LocalDateTime localDateTime)
    {
        BigDecimal integerPart = new BigDecimal(startLocalDate()
                .until(localDateTime.toLocalDate(), ChronoUnit.DAYS) - PLUS_DAYS);
        int second = localDateTime.toLocalTime().toSecondOfDay();
        BigDecimal fractionalPart = second > 0 ? new BigDecimal(second).multiply(intervalBySecond()) : BigDecimal.ZERO;
        return integerPart.add(fractionalPart);
    }

    public static LocalDateTime cellDate2LocalDateTime(
            ReadCellData cellData ,ExcelContentProperty excelContentProperty){
        if (cellData == null){
            return null;
        }else if (cellData.getType() == CellDataTypeEnum.NUMBER){
            if (cellData.getNumberValue() == null){
                return null;
            }
            return number2LocalDateTime(cellData.getNumberValue());
        }else if (cellData.getType() == CellDataTypeEnum.STRING){
            String value = cellData.getStringValue();
            if (org.apache.commons.lang3.StringUtils.isBlank(value)){
                return null;
            }
            String dateFormat = Optional.ofNullable(excelContentProperty.getDateTimeFormatProperty())
                    .map(DateTimeFormatProperty::getFormat)
                    .orElse("yyyy-MM-dd HH:mm:ss");
            int length = dateFormat.length();
            value = value.length() > length ? value.substring(0 ,length) : value;
            try {
                return LocalDateTime.parse(value ,DateTimeFormatter.ofPattern(dateFormat));
            }catch (DateTimeParseException e){
                throw new IllegalArgumentException(MessageFormat.format("列{0}时间格式应该为{1}",
                        parseColumnName(excelContentProperty) ,dateFormat));
            }
        }
        throw new IllegalArgumentException(MessageFormat.format("列{0}格式不支持" ,parseColumnName(excelContentProperty)));
    }

    public static LocalDate cellDate2LocalDate(ReadCellData cellData ,ExcelContentProperty excelContentProperty)
    {
        if (cellData == null){
            return null;
        }else if (cellData.getType() == CellDataTypeEnum.NUMBER){
            LocalDateTime localDateTime = number2LocalDateTime(cellData.getNumberValue());
            return localDateTime == null ? null : localDateTime.toLocalDate();
        }else if (cellData.getType() == CellDataTypeEnum.STRING){
            String value = cellData.getStringValue();
            if (StringUtils.isBlank(value)){
                return null;
            }
            String dateFormat = Optional.ofNullable(excelContentProperty.getDateTimeFormatProperty())
                    .map(DateTimeFormatProperty::getFormat)
                    .orElse("yyyy-MM-dd");
            int length = dateFormat.length();
            value = value.length() > length ? value.substring(0 ,length) : value;
            try {
                return LocalDate.parse(value ,DateTimeFormatter.ofPattern(dateFormat));
            }catch (DateTimeParseException e){
                throw new IllegalArgumentException(MessageFormat.format("列{0}时间格式应该为{1}" ,
                        parseColumnName(excelContentProperty) ,dateFormat));
            }
        }
        throw new IllegalArgumentException(MessageFormat.format("列{0}格式不支持" ,
                parseColumnName(excelContentProperty)));
    }

    private static String parseColumnName(ExcelContentProperty excelContentProperty){
//        List<String> headNameList = excelContentProperty.getHead().getHeadNameList();
//        String column = "";
//        if (CollectionUtils.isNotEmpty(headNameList)){
//            column = headNameList.get(0);
//        }
//        return column;
        return "";
    }

    private static LocalDate startLocalDate(){
        return LocalDate.of(1900, 1, 1);
    }

    private static BigDecimal intervalBySecond(){
        return new BigDecimal(1d / (24 * 60 * 60));
    }

}
